﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Scheduling.Models.Interfaces;
using Scheduling.Models.Repos.Database.Objects;
using Scheduling.Models.Helpers;
using Scheduling.Models.Extensions;
using System.Globalization;

namespace Scheduling.Models.Repos.Database
{
    public class DatabaseBookingLoader : IBookingLoader
    {
        public List<Booking> GetBookings()
        {
            var db = DBContext.Create();
            var q = from b in db.Bookings
                    join ci in db.CourseInstances on b.CourseInstanceID equals ci.ID
                    join r in db.Rooms on b.RoomID equals r.ID
                    join u in db.Users on b.UserID equals u.ID
                    orderby b.StartDate
                    select new { b, ci, r, u };
            return LinqHelper.GenericMap<Booking>(q, "b", "ci>CourseInstance", "r>Room", "u>User");
        }

        public Booking GetBooking(int BookingID)
        {
            var db = DBContext.Create();
            var q = from b in db.Bookings
                    join ci in db.CourseInstances on b.CourseInstanceID equals ci.ID
                    join r in db.Rooms on b.RoomID equals r.ID
                    join u in db.Users on b.UserID equals u.ID
                    where(b.ID == BookingID)
                    select new { b, ci, r, u };
            if (q.Count() > 0)
                return LinqHelper.GenericMap<Booking>(q, "b", "ci>CourseInstance", "r>Room", "u>User").First();
            else
                return null;
        }

        public List<Booking> GetBookingsByUserID(string UserID)
        {
            var db = DBContext.Create();
            var q = from b in db.Bookings
                    join ci in db.CourseInstances on b.CourseInstanceID equals ci.ID
                    join r in db.Rooms on b.RoomID equals r.ID
                    join u in db.Users on b.UserID equals u.ID
                    orderby b.StartDate
                    where (u.ID == UserID)
                    select new { b, ci, r, u };
            return LinqHelper.GenericMap<Booking>(q, "b", "ci>CourseInstance", "r>Room", "u>User");
        }

        public List<Booking> GetCollisions(Booking Booking)
        {
            var db = DBContext.Create();
            var q = from b in db.Bookings
                    join ci in db.CourseInstances on b.CourseInstanceID equals ci.ID
                    join r in db.Rooms on b.RoomID equals r.ID
                    join u in db.Users on b.UserID equals u.ID
                    where (Booking.EndDate > b.StartDate && Booking.StartDate < b.EndDate && b.RoomID == Booking.Room.ID)
                    select new { b, ci, r, u };
            return LinqHelper.GenericMap<Booking>(q, "b", "ci>CourseInstance", "r>Room", "u>User");
        }

        public void DeleteBooking(int BookingID)
        {
            var db = DBContext.Create();
            var q = from b in db.Bookings select b;
            if(q.Count() > 0)
                db.ExecuteCommand("DELETE FROM Bookings WHERE ID = {0}", BookingID);
        }

        public void InsertBooking(Booking Booking)
        {
            var db = DBContext.Create();
            db.ExecuteCommand(
                "INSERT INTO Bookings(CourseInstanceID, UserID, RoomID, StartDate, EndDate) VALUES({0}, '{1}', '{2}', '{3}', '{4}')".SqlFormat(
                    Booking.CourseInstance.ID, Booking.User.ID, Booking.Room.ID, Booking.StartDate.ToString(CultureInfo.InvariantCulture), Booking.EndDate.ToString(CultureInfo.InvariantCulture)
                ));
        }
        public void EditBooking(Booking booking)
        {
            DeleteBooking(booking.ID);
            InsertBooking(booking);
        }


        public List<Booking> GetBookingByCourseID(string CourseID)
        {
            var db = DBContext.Create();
            var q = from c in db.Courses
                    join ci in db.CourseInstances on c.ID equals ci.CourseID
                    join b in db.Bookings on ci.ID equals b.CourseInstanceID
                    join r in db.Rooms on b.RoomID equals r.ID
                    join u in db.Users on b.UserID equals u.ID
                    where (c.ID==CourseID)
                    orderby b.StartDate
                    select new { b, ci, r, u };
            return LinqHelper.GenericMap<Booking>(q, "b", "ci>CourseInstance", "r>Room", "u>User");
        }
        public void Clear()
        {
            throw new NotImplementedException();
        }


        public List<Booking> GetBookingsByCourseInstanceID(int CourseInstanceID)
        {
            var db = DBContext.Create();
            var q = from ci in db.CourseInstances
                    join c in db.Courses on ci.CourseID equals c.ID
                    join b in db.Bookings on ci.ID equals b.CourseInstanceID
                    join r in db.Rooms on b.RoomID equals r.ID
                    join u in db.Users on b.UserID equals u.ID
                    where (ci.ID == CourseInstanceID)
                    orderby b.StartDate
                    select new { b, ci, r, u };
            return LinqHelper.GenericMap<Booking>(q, "b", "ci>CourseInstance", "c>Course", "r>Room", "u>User");
        }
    }
}